package mytree

import (
	"fmt"
	"sort"

	"gitee.com/hailib/mytree/errs"
	"gitee.com/hailib/mytree/internal"
	_ "github.com/go-sql-driver/mysql" // 引入 mysql 驱动
	"github.com/jmoiron/sqlx"
	gouuid "github.com/satori/go.uuid"
)

// mysql 树状结构
type mytree struct {
	*internal.Database
	branchTable     string   // 分支表的名称
	leafTable       string   // 叶子表的名称
	maxBranch       Quantity // 树所拥有的最大分枝数量
	maxDirectBranch Quantity // 分支允许拥有的最大子分支数量
}

// New 创建新的树的实例
// 参数： 数据源名称，闭合表的名称，数据表的名称
func New(
	conn *sqlx.DB, // 数据库连接
	branchTable string, // 分支表
	leafTable string, // 叶子表
	maxBranch Quantity, // 树最大分枝数量
	maxDirectBranch Quantity, // 最大子分支数量
	logger internal.Logger, // 日志
) (MyTree, Error) {
	db := internal.NewDatabase(conn, logger)

	tree := mytree{
		Database:        db,
		branchTable:     branchTable,
		leafTable:       leafTable,
		maxBranch:       maxBranch,
		maxDirectBranch: maxDirectBranch,
	}
	err := tree.initTables()
	if err != nil {
		return nil, err
	}
	return &tree, nil
}

// NewDefault 使用默认的日志创建新的树的实例
// 参数： 数据源名称，闭合表的名称，数据表的名称
func NewDefault(dataSourceName string, branchTable, leafTable string) (MyTree, Error) {
	conn, terr := sqlx.Connect("mysql", dataSourceName)
	if terr != nil {
		return nil, errs.New(errs.DatabaseError, 210901171059, "dsn: %s", dataSourceName)
	}
	return New(conn, branchTable, leafTable, 100, 1000, internal.DefaultLogger())
}

// initTables 初始化表，
//
// 如果表不存在则创建，如果表存在则跳过
func (m *mytree) initTables() Error {
	branch, leaf, err := internal.ReadTemplate(m.branchTable, m.leafTable)
	if err != nil {
		return err
	}

	tx, err := m.Begin("init tables")
	if err != nil {
		return err
	}
	_, err = tx.Exec(branch)
	if err != nil {
		return err
	}

	_, err = tx.Exec(leaf)
	if err != nil {
		return err
	}
	return tx.Commit()
}

// AddBranch 添加一个新的分支
func (m *mytree) AddBranch(parentId Id, name string, ordinal int64) (branchId Id, err Error) {
	err = CheckBranchName(name) // 如称校验
	if err != nil {
		return IdNil, err
	}

	parent, tx, root, err := m.lock(parentId, "add branch, to: %d, name: %s", parentId, name)
	if err != nil {
		return IdNil, err
	}

	if root.OwnBranch >= m.maxBranch { // 如果树的分支太多
		return IdNil, tx.Rollbackf(errs.TooManyChildren, 210901161421, "tree branch: %d", root.OwnBranch)
	}

	if parent.DirectBranch >= m.maxDirectBranch { // 如果父分支的子节点太多
		return IdNil, tx.Rollbackf(errs.TooManyChildren, 210901161512, "children: %d", parent.DirectBranch)
	}

	branchId, err = m.addBranch(tx, parent.Owner, parent.RootId, parent.Id, parent.Layer, name, ordinal)
	if err != nil {
		return IdNil, err
	}

	err = m.updateBranch(tx, parent.Id)
	if err != nil {
		return IdNil, err
	}

	err = tx.Commit()
	if err != nil {
		return IdNil, err
	}

	return branchId, nil
}

// addBranch 在事务里添加一个分支
//
// 1.如果添加成功，事务并不会被提交
// 2.如果添加失败事务将会被回滚
// 3.如果 rootId 为空，分支的 rootId 将会被设为自已
func (m *mytree) addBranch(tx internal.Tx, ownerId, rootId, parentId Id, parentLayer Layer, name string, ordinal int64) (branchId Id, err Error) {
	uuid := gouuid.NewV4().String()
	layer := LayerNil
	cmd := "INSERT INTO `" + m.branchTable + "` SET `owner`=?, `uuid`=?, `root_id`=?, `parent_id`=?, `layer`=?, `name`=?, `ordinal` = ?"

	res, err := tx.Exec(cmd, ownerId, uuid, rootId, parentId, layer, name, ordinal)
	if err != nil {
		return IdNil, err
	}

	insertId, nerr := res.LastInsertId()
	if nerr != nil {
		return IdNil, tx.Rollbackf(errs.DbQueryError, 210901162918, "get last insert id error, error: %s", nerr.Error())
	}

	branchId, err = IdFromI64(insertId)
	if err != nil {
		return IdNil, tx.Rollback(err)
	}

	// 更新自身的层级、父分组及排序
	update := "UPDATE `" + m.branchTable + "` SET `root_id` = ? , `layer`= ? , `ordinal` = ? WHERE `branch_id` = ?"
	layer = parentLayer.Join(branchId)

	if rootId.IsNull() {
		rootId = branchId
		ordinal = branchId.I64()
	}

	effect, err := tx.Exec(update, rootId, layer, ordinal, branchId)
	if err != nil {
		return IdNil, err
	}

	_, err = tx.CheckRowsAffected(effect, 1, 1)
	if err != nil {
		return IdNil, err
	}

	return branchId, nil
}

// // IsExists 检查分支是否存在
// func (m *mytree) IsBranchExists(branchId Id) (bool, error) {
// 	type checker struct {
// 		Exists bool `db:"exists"`
// 	}
// 	c := checker{}
// 	cmd := "SELECT IFNULL ( (SELECT 1 FROM `" + m.branchTable + "` WHERE `branch_id` = ? LIMIT 1) , 0) AS 'exists'"
// 	err := m.Get(nil, &c, cmd)
// 	if err != nil {
// 		return false, err
// 	}
// 	return c.Exists, nil
// }

func (m *mytree) DB() *sqlx.DB {
	return m.Database.DB
}

// DelBranch 直接删除分支
func (m *mytree) DelBranch(branchId Id) (int64, Error) {
	branch, tx, _, err := m.lock(branchId, "del branch: %d", branchId)
	if err != nil {
		return 0, err
	}

	del := "DELETE FROM `" + m.branchTable + "` WHERE `layer` LIKE ?"
	result, err := tx.Exec(del, branch.Layer+"%")
	if err != nil {
		return 0, err
	}

	count, err := tx.RowsAffected(result)
	if err != nil {
		return 0, err
	}

	if branch.HasParent() {
		err = m.updateBranch(tx, branchId)
		if err != nil {
			return 0, err
		}
	}
	err = tx.Commit()
	if err != nil {
		return 0, err
	}
	return count, nil
}

// DelTreeBranch 删除某棵树的子分支
func (m *mytree) DelTreeBranch(rootId Id, branchId Id) (int64, Error) {
	branch, tx, root, err := m.lock(branchId, "del branch: %d", branchId)
	if err != nil {
		return 0, err
	}

	if root.Id != rootId {
		return 0, tx.Rollbackf(errs.InvalidParam, 210906141403, "check root fail, branch: %d, root: %d, input root: %d", branchId, branch.RootId, rootId)
	}

	del := "DELETE FROM `" + m.branchTable + "` WHERE `layer` LIKE ?"
	result, err := tx.Exec(del, branch.Layer+"%")
	if err != nil {
		return 0, err
	}

	count, err := tx.RowsAffected(result)
	if err != nil {
		return 0, err
	}

	if branch.HasParent() {
		err = m.updateBranch(tx, branchId)
		if err != nil {
			return 0, err
		}
	}
	err = tx.Commit()
	if err != nil {
		return 0, err
	}
	return count, nil
}

// getByIdForUpdate 根据 id 查询分支
// 如果查询失败事务将会被自动回滚
func (m *mytree) getByIdForUpdate(tx internal.Tx, branchId Id) (*Branch, Error) {
	if branchId.IsNull() {
		return nil, errs.New(errs.InvalidParam, 210902102101, "branch id is nil")
	}
	branch := Branch{}
	cmd := "SELECT * FROM `" + m.branchTable + "` WHERE `branch_id` = ? FOR UPDATE"
	err := tx.Get(&branch, cmd, branchId)
	if err != nil {
		return nil, err
	}
	return &branch, nil
}

// getById 根据 id 查询分支
func (m *mytree) GetById(branchId Id) (*Branch, Error) {
	if branchId.IsNull() {
		return nil, errs.New(errs.InvalidParam, 210901154638, "branch id is nil")
	}
	branch := Branch{}
	cmd := "SELECT * FROM `" + m.branchTable + "` WHERE `branch_id` = ?"
	err := m.Get(&branch, cmd, branchId)
	if err != nil {
		return nil, err
	}
	return &branch, nil
}

// lock 锁定分支 id 所属的树，并返回分支、根分支信息
func (m *mytree) lock(branchId Id, desc string, args ...interface{}) (branch *Branch, tx internal.Tx, root *Branch, err Error) {
	reason := fmt.Sprintf(desc, args...)

	tx, err = m.Begin(reason)
	if err != nil {
		return nil, nil, nil, err
	}

	branch, root, err = m.lockByTx(tx, branchId)
	if err != nil {
		return nil, nil, nil, err
	}

	return branch, tx, root, nil
}

// lockByTx 使用给定的事务锁定树的根节点
//   * 如果锁定失败，事务将会被回滚
func (m *mytree) lockByTx(tx internal.Tx, branchId Id) (branch *Branch, root *Branch, err Error) {
	branch, err = m.GetById(branchId) // 获取分支以得到父分支信息
	if err != nil {
		return nil, nil, tx.Rollback(err)
	}

	root, err = m.getByIdForUpdate(tx, branch.RootId)
	if err != nil {
		return nil, nil, err
	}

	branch, err = m.GetById(branchId) // 再次查询一次分支，以确保分支存在
	if err != nil {
		return nil, nil, tx.Rollback(err)
	}
	return branch, root, nil
}

// MoveBranch 移动分支
//   1. 移动分支时并不会检查树的总分支数
//   2. 移动分支时，如果父节点的子分支数量太多，将会返回一个错误
//   3. 如果父分支没有发生变化则直接返回
//   4. 如果根分支不一样将返回错误
//   5. 不能将自已移动到自已的后代分支下面
func (m *mytree) MoveBranch(branchId Id, parentId Id) Error {
	branch, tx, _, err := m.lock(branchId, "mv: %d <- %d", branchId, parentId)
	if err != nil {
		return err
	}

	if branch.ParentId == parentId { // 不需要操作
		return tx.Rollback(nil) // 只是回滚事务，放弃操作，并没有错误
	}

	currentParentId := branch.ParentId // 旧的父分支 id

	newParent, err := m.GetById(parentId)
	if err != nil {
		return tx.Rollback(err)
	}

	if newParent.RootId != branch.RootId {
		return tx.Rollbackf(errs.InvalidParam, 210901181220, "root id not equal, parent: %d, branch: %d", newParent.RootId, branch.RootId)
	}

	if newParent.DirectBranch > m.maxDirectBranch {
		return tx.Rollbackf(errs.TooManyChildren, 210901181353, "direct branch: %d", newParent.DirectBranch)
	}

	if newParent.IsChildOf(branch) {
		return tx.Rollbackf(errs.InvalidParam, 210901170843, "illegal param")
	}

	setParent := "UPDATE `" + m.branchTable + "` SET `parent_id` = ? WHERE `branch_id` = ?"
	_, err = tx.Exec(setParent, parentId, branchId)
	if err != nil {
		return err
	}

	// 更新自身及所有下级分支的 layer
	updateLayer := "UPDATE `" + m.branchTable + "` SET `layer` = CONCAT( ? , SUBSTRING(`layer`, ?) ) WHERE `layer` LIKE ?"
	_, err = tx.Exec(updateLayer, newParent.Layer, len(branch.Layer)-len(branch.Id.String()), fmt.Sprintf("%s%%", branch.Layer))
	if err != nil {
		return err
	}

	err = m.updateBranch(tx, currentParentId) // 更新旧的父节点
	if err != nil {
		return err
	}

	err = m.updateBranch(tx, parentId) // 更新新的父节点
	if err != nil {
		return err
	}

	return tx.Commit()
}

// MoveBranch 移动分支
//   1. 移动分支时并不会检查树的总分支数
//   2. 移动分支时，如果父节点的子分支数量太多，将会返回一个错误
//   3. 如果父分支没有发生变化则直接返回
//   4. 如果根分支不一样将返回错误
//   5. 不能将自已移动到自已的后代分支下面
func (m *mytree) MoveBranchTx(stx *sqlx.Tx, branchId Id, parentId Id) Error {
	tx := internal.NewTxf(stx, m.Logger, "MoveBranchTx(tx,%d, %d)", branchId, parentId)
	branch, _, err := m.lockByTx(tx, branchId)
	if err != nil {
		return err
	}

	if branch.ParentId == parentId { // 不需要操作
		return tx.Rollback(nil) // 只是回滚事务，放弃操作，并没有错误
	}

	currentParentId := branch.ParentId // 旧的父分支 id

	newParent, err := m.GetById(parentId)
	if err != nil {
		return tx.Rollback(err)
	}

	if newParent.RootId != branch.RootId {
		return tx.Rollbackf(errs.InvalidParam, 210901181220, "parent.root:%d != branch.root:%d", newParent.RootId, branch.RootId)
	}

	if newParent.DirectBranch > m.maxDirectBranch {
		return tx.Rollbackf(errs.TooManyChildren, 210901181353, "parent children: %d", newParent.DirectBranch)
	}

	if newParent.IsChildOf(branch) {
		return tx.Rollbackf(errs.InvalidParam, 210901170843, "illegal param")
	}

	setParent := "UPDATE `" + m.branchTable + "` SET `parent_id` = ? WHERE `branch_id` = ?"
	_, err = tx.Exec(setParent, parentId, branchId)
	if err != nil {
		return err
	}

	// 更新自身及所有下级分支的 layer
	updateLayer := "UPDATE `" + m.branchTable + "` SET `layer` = CONCAT( ? , SUBSTRING(`layer`, ?) ) WHERE `layer` LIKE ?"
	_, err = tx.Exec(updateLayer, newParent.Layer, len(branch.Layer)-len(branch.Id.String()), fmt.Sprintf("%s%%", branch.Layer))
	if err != nil {
		return err
	}

	err = m.updateBranch(tx, currentParentId) // 更新旧的父节点
	if err != nil {
		return err
	}

	err = m.updateBranch(tx, parentId) // 更新新的父节点
	if err != nil {
		return err
	}

	return tx.Commit()
}

// NewTree 添加新的树，一个所有者允许有多棵树，每棵树之间互不干扰
//
// owner 所有者
// name  不允许包含如下特殊字符串： "/"
func (m *mytree) NewTree(owner Id, name string) (branchId Id, err Error) {
	if owner.IsNull() {
		return IdNil, errs.New(errs.InvalidParam, 210901154557, "owner is %d", owner)
	}

	err = CheckBranchName(name)
	if err != nil {
		return IdNil, err
	}

	tx, err := m.Beginf("NewTree, owner: %d, name: %s", owner, name)
	if err != nil {
		return IdNil, err
	}

	branchId, err = m.addBranch(tx, owner, IdNil, IdNil, "", name, 0)
	if err != nil {
		return IdNil, err
	}

	err = tx.Commit()
	if err != nil {
		return IdNil, err
	}

	return branchId, nil
}

// NewTreeTx 添加新的树，一个所有者允许有多棵树，每棵树之间互不干扰
//
// 如果分支添加失败，事务将会被回滚
// owner 所有者
// name  不允许包含如下特殊字符串： "/"
func (m *mytree) NewTreeTx(tx *sqlx.Tx, owner Id, name string) (branchId Id, err Error) {
	if owner.IsNull() {
		return IdNil, errs.New(errs.InvalidParam, 210901154557, "owner is %d", owner)
	}

	err = CheckBranchName(name)
	if err != nil {
		return IdNil, err
	}

	t := internal.NewTxf(tx, m.Logger, "NewTreeTx(tx, %d, %s)", owner, name)

	branchId, err = m.addBranch(t, owner, IdNil, IdNil, "", name, 0)
	if err != nil {
		return IdNil, err
	}

	return branchId, nil
}

// SelectTree 获取某棵树的所有分支
func (m *mytree) SelectTree(branchId Id) ([]*Branch, Error) {
	var branchs []*Branch
	sel := "SELECT * FROM `" + m.branchTable + "` WHERE root_id = ?"
	err := m.Select(&branchs, sel, branchId)
	if err != nil {
		return nil, err
	}
	return branchs, nil
}

// SelectBranch 查找分支自身及所有的下级分支
func (m *mytree) SelectBranch(branchId Id) ([]*Branch, Error) {
	branch, err := m.GetById(branchId)
	if err != nil {
		return nil, err
	}

	sel := "SELECT * FROM `" + m.branchTable + "` WHERE `layer` LIKE ?"
	var branchs []*Branch
	err = m.Select(&branchs, sel, fmt.Sprintf("%s%%", branch.Layer))
	if err != nil {
		return nil, err
	}
	return branchs, nil
}

// SetBranch 设置分支属性
func (m *mytree) SetBranch(branchId Id, name string, ordinal int64, clientdata string) Error {
	update := "UPDATE `" + m.branchTable + "` SET `name` = ? , `ordinal` = ? , `client_data` = ? WHERE `branch_id` = ?"
	_, err := m.Exec(update, name, ordinal, clientdata, branchId)
	if err != nil {
		return err
	}
	return nil
}

// SetOrdinal 设置一系列分组的顺序
//   * 如果设置失败事务将会被回滚
//   * 如果设置成功，事务不会被提交
func (m *mytree) setOrdinal(tx internal.Tx, parent Id, ordinals map[Id]int64) (int64, Error) {
	update := "UPDATE `" + m.branchTable + "` SET `ordinal` = ? WHERE `parent_id` = ? AND `branch_id` = ?"
	stmt, err := tx.Prepare(update)
	if err != nil {
		return 0, err
	}

	var count int64 = 0

	for bid, ord := range ordinals {
		result, err := stmt.Exec(ord, parent, bid)
		if err != nil {
			return 0, err
		}
		n, err := tx.RowsAffected(result)
		if err != nil {
			return 0, err
		}
		count = count + n
	}

	err = stmt.Close()
	if err != nil {
		return 0, err
	}

	return count, nil
}

// SetOrdinal 设置一系列分组的顺序
func (m *mytree) SetOrdinal(parentId Id, ordinals map[Id]int64) (int64, Error) {
	tx, err := m.Beginf("SetOrdinal(%d, ordinals)", parentId)
	if err != nil {
		return 0, err
	}

	count, err := m.setOrdinal(tx, parentId, ordinals)
	if err != nil {
		return 0, err
	}

	err = tx.Commit()
	if err != nil {
		return 0, err
	}

	return count, nil
}

// SetOrdinal 设置某个分支的直接子分支的顺序
func (m *mytree) SetOrdinalTx(stx *sqlx.Tx, parentId Id, ordinals map[Id]int64) (int64, Error) {
	tx := internal.NewTxf(stx, m.Logger, "SetOrdinalTx(tx, %d, ordinals)", parentId)
	count, err := m.setOrdinal(tx, parentId, ordinals)
	if err != nil {
		return 0, err
	}
	return count, nil
}

// updateBranch 在事务里更新分支的信息
//   * 本函数只负责更新数所，不对树加锁
//   * 如果更新成功，事务并不会被提交
//   * 如果更新失败，事务将被回滚
//   * 会更新如下列： 自身直接拥有的 子分支及叶子，自身的后代分支及叶子
//   * 后代的叶子将会被去重
func (m *mytree) updateBranch(tx internal.Tx, branchId Id) Error {
	branch, err := m.GetById(branchId)
	if err != nil {
		return tx.Rollback(err)
	}

	var ownLeaf int = 0

	ownLeafCounter := "SELECT COUNT(DISTINCT leaf.leaf) " +
		" FROM device_group_data AS `branch` " +
		" INNER JOIN device_group_map AS leaf " +
		" ON branch.branch_id = leaf.branch_id " +
		" WHERE branch.layer LIKE ? "
	err = tx.Get(&ownLeaf, ownLeafCounter, branch.Layer+"%")
	if err != nil {
		return err
	}

	countCmd := "SELECT " +
		" COUNT(*)                 AS `direct_branch_count` " +
		" , (SELECT COUNT(*) AS leafs FROM `" + m.leafTable + "` WHERE `branch_id` = ?) AS `direct_leaf_count`  " +
		" , SUM(`own_branch_count`)  AS `own_branch_count`    " +
		" FROM `" + m.branchTable + "` WHERE `parent_id` = ?"

	count := internal.Statistics{}
	err = tx.Get(&count, countCmd, branchId, branchId)
	if err != nil {
		return err
	}

	cmd := "UPDATE `" + m.branchTable + "` SET " +
		" `direct_branch_count` = ?, " +
		" `direct_leaf_count`   = ?, " +
		" `own_branch_count`    = ?, " +
		" `own_leaf_count`      = ?  " +
		" WHERE branch_id = ? "

	_, err = tx.Exec(cmd, count.DirectBranch(), count.DirectLeaf(), count.OwnBranch(), ownLeaf, branchId)
	if err != nil {
		return err
	}

	if branch.HasParent() {
		return m.updateBranch(tx, branch.ParentId)
	} else {
		return nil
	}
}

// AddLeafs 添加一系列的叶子
func (m *mytree) addLeafs(tx internal.Tx, branchId Id, rootId Id, leafs []Id) Error {
	insert := "INSERT INTO `" + m.leafTable + "` SET `branch_id` = ? , `leaf` = ?, `root_id` = ? "
	stmt, err := tx.Prepare(insert)
	if err != nil {
		return err
	}

	for _, leaf := range leafs {
		_, err = stmt.Exec(branchId, leaf, rootId)
		if err != nil {
			return err
		}
	}

	err = stmt.Close()
	if err != nil {
		return err
	}

	err = m.updateBranch(tx, branchId)
	if err != nil {
		return err
	}

	return nil
}

// AddLeafs 添加一系列的叶子
func (m *mytree) AddLeafs(branchId Id, leafs ...Id) Error {
	branch, tx, _, err := m.lock(branchId, "add leaf: %v", leafs)
	if err != nil {
		return err
	}

	err = m.addLeafs(tx, branchId, branch.RootId, leafs)
	if err != nil {
		return err
	}

	err = tx.Commit()
	if err != nil {
		return err
	}

	return nil
}

// AddLeafsTx 添加一系列的叶子
//   * 如果执行失败，事务将会被回滚
func (m *mytree) AddLeafsTx(stx *sqlx.Tx, branchId Id, leafs ...Id) Error {
	tx := internal.NewTxf(stx, m.Logger, "AddLeafsTx(tx, %d, leafs)", branchId)
	branch, _, err := m.lockByTx(tx, branchId)
	if err != nil {
		return err
	}

	err = m.addLeafs(tx, branchId, branch.RootId, leafs)
	if err != nil {
		return err
	}

	return nil
}

// DelLeafs 删除某个分支下面的一系列叶子, 并返回实际删除的 叶子 列表
func (m *mytree) delLeafsOfBranch(tx internal.Tx, rootId Id, branchId Id, leafs []Id) ([]Id, Error) {
	ids := make([]Id, 0)
	del := "DELETE FROM `" + m.leafTable + "` WHERE `root_id` = ? AND `branch_id` = ? AND `leaf` = ?"
	stmt, err := tx.Prepare(del)
	if err != nil {
		return nil, err
	}

	for _, leaf := range leafs {
		ro, err := stmt.Exec(rootId, branchId, leaf)
		if err != nil {
			return nil, err
		}
		n, err := tx.RowsAffected(ro)
		if err != nil {
			return nil, err
		}
		if n > 0 { // 如果受响影的行数大于 0 则认为删除成功
			ids = append(ids, leaf)
		}
	}

	err = stmt.Close()
	if err != nil {
		return nil, err
	}

	err = m.updateBranch(tx, branchId)
	if err != nil {
		return nil, err
	}

	return ids, nil
}

// DelLeafsOfBranch 删除某个分支下面的一系列叶子，并返回实际被删除的 id 列表
func (m *mytree) DelLeafsOfBranch(rootId Id, branchId Id, leafs ...Id) ([]Id, Error) {
	_, tx, _, err := m.lock(branchId, "del leaf, branch: %d, leaf: %v", branchId, leafs)
	if err != nil {
		return nil, err
	}

	ids, err := m.delLeafsOfBranch(tx, rootId, branchId, leafs)
	if err != nil {
		return nil, err
	}

	err = tx.Commit()
	if err != nil {
		return nil, err
	}
	return ids, nil
}

// DelLeafsOfBranchTx 删除某个分支下面的一系列叶子，并返回实际被删除的 id 列表
//   * 如果删除失败，事务会被自动回滚
//   * 如果删除成功，事务并不会自动提交
func (m *mytree) DelLeafsOfBranchTx(stx *sqlx.Tx, rootId Id, branchId Id, leafs ...Id) ([]Id, Error) {
	tx := internal.NewTxf(stx, m.Logger, "DelLeafsTx(tx, %d, %v)", branchId, leafs)
	_, _, err := m.lockByTx(tx, branchId)
	if err != nil {
		return nil, err
	}

	ids, err := m.delLeafsOfBranch(tx, rootId, branchId, leafs)
	if err != nil {
		return nil, err
	}
	return ids, nil
}

// DelLeafTx 从整个数据库中删除叶子，并返回受影响的分支数组
//   * 如果删除失败，事务将会被回滚
//   * 删除过程中，叶子所属的所有树都会被锁定
func (m *mytree) DelLeafTx(stx *sqlx.Tx, leafId Id) ([]Id, Error) {
	roots, err := m.SelectLeafRoots(leafId)
	if err != nil {
		return nil, err
	}
	ids := make([]Id, 0)

	tx := internal.NewTxf(stx, m.Logger, "DelLeafTx(tx, %d)", leafId)
	sort.Sort(IdList(roots)) // 由于删除树内容需要锁树，因此需要排队避免死锁
	for _, root := range roots {
		leafs, err := m.SelectLeafBranchsTx(stx, root, leafId) // 找出设备所属的所有分组
		if err != nil {
			return nil, err
		}
		_, err = m.getByIdForUpdate(tx, root) // 锁树
		if err != nil {
			return nil, err
		}
		for _, leaf := range leafs {
			arr, err := m.delLeafsOfBranch(tx, root, leaf.BranchId, []Id{leafId}) // 删除叶子
			if err != nil {
				return nil, err
			}
			err = m.updateBranch(tx, leaf.BranchId) // 更新分支
			if err != nil {
				return nil, err
			}
			if len(arr) > 0 {
				// 如果实际删除了，就把受影响的 id 添加进去
				ids = append(ids, leaf.BranchId)
			}
		}
	}
	return ids, nil
}

// SelectLeafBranchs 查找叶子所属的所有树
func (m *mytree) SelectLeafBranchs(rootId Id, leaf Id) ([]*Leaf, Error) {
	var leafs []*Leaf
	sel := "SELECT * FROM `" + m.leafTable + "` WHERE root_id = ? AND leaf = ?"
	err := m.Select(&leafs, sel, rootId, leaf)

	if err != nil {
		return nil, err
	}
	return leafs, nil
}

// SelectLeafBranchsTx 查找叶子所属的所有树
//   1. 如果查询失败事务被自动回滚，但查询成功时事务不会被自动提交
func (m *mytree) SelectLeafBranchsTx(stx *sqlx.Tx, rootId Id, leaf Id) ([]*Leaf, Error) {
	tx := internal.NewTxf(stx, m.Logger, "SelectLeafBranchsTx(tx, %d, %d)", rootId, leaf)
	var leafs []*Leaf
	sel := "SELECT * FROM `" + m.leafTable + "` WHERE root_id = ? AND leaf = ?"
	err := tx.Select(&leafs, sel, rootId, leaf)
	if err != nil {
		return nil, err
	}
	return leafs, nil
}

// SelectLeafRoots 查找叶子所属的所有根节点
func (m *mytree) SelectLeafRoots(leaf Id) ([]Id, Error) {
	var leafs []*Leaf
	sel := "SELECT `root_id` FROM `" + m.leafTable + "` WHERE `leaf` = ? GROUP BY `root_id`"
	err := m.Select(&leafs, sel, leaf)
	if err != nil {
		return nil, err
	}

	arr := make([]Id, len(leafs))
	for i, l := range leafs {
		arr[i] = l.Root
	}
	return arr, nil
}

// SelectBranchLeafs 查找分支直接拥有的叶子
func (m *mytree) SelectBranchLeafs(branchId Id) ([]*Leaf, Error) {
	var leafs []*Leaf
	sel := "SELECT * FROM `" + m.leafTable + "` WHERE branch_id = ?"
	err := m.Select(&leafs, sel, branchId)

	if err != nil {
		return nil, err
	}
	return leafs, nil
}

// SelectBranchLeafsTx 查找分支直接拥有的叶子
func (m *mytree) SelectBranchLeafsTx(stx *sqlx.Tx, branchId Id) ([]*Leaf, Error) {
	tx := internal.NewTxf(stx, m.Logger, "SelectBranchLeafsTx(tx, %d)", branchId)
	var leafs []*Leaf
	sel := "SELECT * FROM `" + m.leafTable + "` WHERE branch_id = ?"
	err := tx.Select(&leafs, sel, branchId)
	if err != nil {
		return nil, err
	}

	return leafs, nil
}

// SelectBranchLeafPage 分页查找分支直接拥有的叶子
//  1. page: 需要显示的页面，从 0 开始
//  2. size: 每页多少条记录
func (m *mytree) SelectBranchLeafPage(branchId Id, page int, size int) ([]*Leaf, Error) {
	var leafs []*Leaf
	sel := "SELECT * FROM `" + m.leafTable + "` WHERE branch_id = ? LIMIT ?,?"
	err := m.Select(&leafs, sel, branchId, page*size, page)
	if err != nil {
		return nil, err
	}
	return leafs, nil
}
